﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Mrc.Data;
using System.Linq;
using System.Data;
using Mrc.Entity;
using Microsoft.Extensions.FileProviders;
using System.IO;

namespace Mrc.Application.Helper
{

    public class DBBackHelper
    {
        /// <summary>
        /// 添加备份
        /// </summary>
        /// <returns></returns>
        public static SysDBBackup DBBack()
        {
            string path = $@"{Directory.GetCurrentDirectory()}\DBBackFile";
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }          
            SysDBBackup sysDBBackup = new SysDBBackup();
            var dbInfo=GetConnectionInfo();
            ServerConnection serverConnection = new ServerConnection(dbInfo.Host,dbInfo.User,dbInfo.Pasword);
            Server server = new Server(serverConnection);
            Database database = server.Databases[dbInfo.DBName];
            Backup backup = new Backup();
            backup.Action = BackupActionType.Database;
            backup.BackupSetDescription = $"{dbInfo.DBName} full backup";
            backup.BackupSetName = $"{dbInfo.DBName} backup";
            backup.Database =dbInfo.DBName;
            BackupDeviceItem deviceItem = new BackupDeviceItem($@"{path}\{dbInfo.DBName}{DateTime.Now.ToString("yyyyMMddHHmmss")}{(new Random().Next(10000, 99999)).ToString()}.bak", DeviceType.File);
            backup.Devices.Add(deviceItem);
            backup.Incremental = false;
            backup.LogTruncation = BackupTruncateLogType.Truncate;
            backup.SqlBackup(server);
            sysDBBackup.Name = DateTime.Now.ToString("yyyyMMddHHmmssfff");
            sysDBBackup.Path = deviceItem.Name;
            FileInfo fileInfo=IOHelper.GetAllFilesInDirectory(path).Where(x => x.FullName == deviceItem.Name).FirstOrDefault();
            sysDBBackup.Size = decimal.Parse(((decimal)fileInfo.Length/ (1024M * 1024M)).ToString());
            return sysDBBackup;
        }


        /// <summary>
        /// 还原备份
        /// </summary>
        /// <param name="backupPath"></param>
        public static void RestoreDatabase(string backupPath)
        {
            var dbInfo = GetConnectionInfo();
            string commandText = $@"USE [master];
            ALTER DATABASE [{dbInfo.DBName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            RESTORE DATABASE [{dbInfo.DBName}] FROM DISK = N'{backupPath}' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;
            ALTER DATABASE [{dbInfo.DBName}] SET MULTI_USER;";
            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
            {
                DataSource = dbInfo.Host,
                InitialCatalog = "master",
                IntegratedSecurity = true,
                 UserID=dbInfo.User,
                 Password=dbInfo.Pasword
            };
            using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();
               //  connection.InfoMessage += Connection_InfoMessage;
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 清空指定数据表
        /// </summary>
        /// <param name="tables"></param>
        /// <returns></returns>
        public static List<string> ClearTables(List<string> tables)
        {
            List<string> hadClearTables = new List<string>();
            var allTables = GetAllTables();
            List<string> sysTable = new List<string> { "IISKeyWords", "SysOrg", "SysOrgType", "SysPost", "SysRole", "SysUser", "SysUserLogOn", "SysUserOrg", "SysDBBackup" };
            tables=tables.Except(sysTable).ToList();
            var connStr = GlobalsConfig.Configuration["db:ConnString"];
            using (SqlConnection Connection = new SqlConnection(connStr))
            {
                Connection.Open();
                foreach (var item in tables)
                {
                    if (!allTables.Contains(item)) continue;
                    string commandText = $@"truncate table {item}";
                    using (SqlCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = commandText;
                        command.CommandType = CommandType.Text;
                        command.ExecuteNonQuery();
                    }
                    hadClearTables.Add(item);
                }
                Connection.Close();
            }
            return hadClearTables;
        }

        /// <summary>
        /// 取该数据库所有表名
        /// </summary>
        /// <returns></returns>
        public static List<string> GetAllTables()
        {
            var connStr = GlobalsConfig.Configuration["db:ConnString"];
            List<string> DBNameList = new List<string>();
            using (SqlConnection Connection = new SqlConnection(connStr))
            {
                Connection.Open();
                SqlCommand comText = new SqlCommand("Select * From Information_Schema.Tables", Connection);//创建Command对象  
                SqlDataReader dr;
                dr = comText.ExecuteReader();//执行查询  
                while (dr.Read())//判断数据表中是否含有数据  
                {
                    DBNameList.Add(dr["TABLE_NAME"].ToString());
                }
                Connection.Close();
            }
            return DBNameList;
        }

        private static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
           // Console.WriteLine(e.Message);
        }
        //"Data Source =192.168.1.23;Initial Catalog =IISLogAnalisys;Persist Security Info=True;User ID=sa;Password=123"
        public static DBInfo GetConnectionInfo()
        {
            DBInfo info = new DBInfo();
            var connStr=GlobalsConfig.Configuration["db:ConnString"];
            var list = connStr.Split(';').ToList();
            foreach (var item in list)
            {
                 var temparry=item.Split('=').ToList();
                 var key = temparry[0];
                 var value = temparry[1];
                if (key.Contains("Data Source"))
                {
                    info.Host = value.Trim();
                    continue;
                }
                else if (key.Contains("Initial Catalog"))
                {
                    info.DBName = value.Trim();
                    continue;
                }
                else if (key.Contains("User ID"))
                {
                    info.User = value.Trim();
                    continue;
                }
                else if (key.Contains("Password"))
                {
                    info.Pasword = value.Trim();
                    continue;
                }
            }
            return info;
        }
    }

    public class DBInfo
    {
        public string Host { get; set; }

        public string User { get; set; }

        public string Pasword { get; set; }

        public string DBName { get; set; }
    }

}
